//
//  JRFMDB+Telen.m
//  TimeTools
//
//  Created by telen on 15/4/27.
//  Copyright (c) 2015年 Telen. All rights reserved.
//

#import "JRFMDB+Telen.h"

@implementation JRFMDatabase (Telen)

// 返回格式：replace into table_name (c1,c2,c3) values (:a,:b,:c);
+(NSString*) Sql_ReplaceDict:(NSDictionary *)record forTable:(NSString *)table
{
    NSArray *columns = [record allKeys];
    NSString *prefix = [NSString stringWithFormat:@"replace into %@ (", table];
    
    NSMutableString *middle = [NSMutableString new];
    for(int i=0;i<[columns count];i++){
        NSString *columnName = [columns objectAtIndex:i];// 列
        if(i>0)[middle appendString:@","];
        [middle appendString:columnName];
    }
    NSString* cuttedMiddle = middle;
    
    NSMutableString *suffix = [NSMutableString new];
    [suffix appendString:@") values ("];
    for(int i=0;i<[columns count];i++){
        NSString *columnName = [columns objectAtIndex:i];// 列名
        if(i>0)[suffix appendString:@","];
        [suffix appendString:@":"];
        [suffix appendString:columnName];
    }
    NSString *cuttedSuffix = suffix;
    
    NSMutableString *sql = [NSMutableString new];
    [sql appendString:prefix];
    [sql appendString:cuttedMiddle];
    [sql appendString:cuttedSuffix];
    [sql appendString:@");"];
    return sql;
}


// 返回格式：insert into table_name (c1,c2,c3) values (:a,:b,:c);
+(NSString*) Sql_InsertDict:(NSDictionary*) record forTable:(NSString*)table
{
    NSArray *columns = [record allKeys];
    NSString *prefix = [NSString stringWithFormat:@"insert into %@ (", table];
    
    NSMutableString *middle = [NSMutableString new];
    for(int i=0;i<[columns count];i++){
        NSString *columnName = [columns objectAtIndex:i];// 列
        if(i>0)[middle appendString:@","];
        [middle appendString:columnName];
    }
    NSString* cuttedMiddle = middle;
    
    NSMutableString *suffix = [NSMutableString new];
    [suffix appendString:@") values ("];
    for(int i=0;i<[columns count];i++){
        NSString *columnName = [columns objectAtIndex:i];// 列名
        if(i>0)[suffix appendString:@","];
        [suffix appendString:@":"];
        [suffix appendString:columnName];
    }
    NSString *cuttedSuffix = suffix;
    
    NSMutableString *sql = [NSMutableString new];
    [sql appendString:prefix];
    [sql appendString:cuttedMiddle];
    [sql appendString:cuttedSuffix];
    [sql appendString:@");"];
    return sql;
}

+ (BOOL)JRFMDB_Clone_Opened_DB:(JRFMDatabase*) db1 toOpened_DB:(JRFMDatabase*) db2
{
    JRFMResultSet *rs =[db1 executeQuery:@"SELECT * FROM sqlite_master WHERE type='table' order by name"];//select name from sqlite_master where type='table' order by namex
    if (rs) {
        while ([rs next]){
            NSString* name = [rs stringForColumn:@"name"];
            NSString* sql = [rs stringForColumn:@"sql"];
            //NSLog(@"%@",sql);
            [db2 executeUpdate:sql];
//            JRFMResultSet *rs_tbInfo = [db1 executeQuery:[NSString stringWithFormat:@"pragma table_info ('%@')",name]];
//            if (rs_tbInfo) {
//                NSMutableString* sql = [NSMutableString new];
//                [sql appendFormat:@"CREATE TABLE IF NOT EXISTS %@ (",name];
//                while ([rs_tbInfo next]) {
//                    NSLog(@"%@",rs_tbInfo.resultDictionary);
//                    NSString* columnKey = [rs_tbInfo stringForColumn:@"name"];
//                    NSString* columnType = [rs_tbInfo stringForColumn:@"type"];
//                    [sql appendFormat:@"%@ %@,",columnKey,columnType];
//                }
//                [sql replaceCharactersInRange:NSMakeRange(sql.length-1, 1) withString:@")"];
//                [db2 executeUpdate:sql];
//            }else{
//                return NO;
//            }
            JRFMResultSet *rs_tb =[db1 executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@",name]];
            if(rs_tb){
                while ([rs_tb next]){
                    //NSLog(@"%@",rs_tb.resultDictionary);
                    NSString* sql = [self Sql_InsertDict:rs_tb.resultDictionary forTable:name];
                    [db2 executeUpdate:sql withParameterDictionary:rs_tb.resultDictionary];
                }
            }else{
                return NO;
            }
        }
    }else{
        return NO;
    }
    return YES;
}


//DB 版本管理
static NSString* tableName_JRFMdbVer = @"DB_Telen_Version";
static NSString* clumName_JRFMdbVer = @"value";
static NSString* clumName_JRFMdbName = @"name";
static NSString* clumKey_JRFMdb = @"DB_Version";
+ (long)JRFMDB_getVersionFromDB:(JRFMDatabase*) db;
{
    NSString* select = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = '%@'",tableName_JRFMdbVer,clumName_JRFMdbName,clumKey_JRFMdb];
    JRFMResultSet* rs_tb = [db executeQuery:select];
    if (rs_tb) {
        while ([rs_tb next]) {
            NSNumber* num = rs_tb.resultDictionary[clumName_JRFMdbVer];
            if (num != nil && [num isKindOfClass:[NSNumber class]]) {
                return [num longValue];
            }
        }
    }
    return -1;
}
+ (void)JRFMDB_setDB:(JRFMDatabase*) db version:(long)ver;
{
    //createTable
    NSMutableString* sql_create = [NSMutableString string];
    [sql_create appendFormat:@"create table  if not exists %@(",tableName_JRFMdbVer];
    [sql_create appendString:clumName_JRFMdbName];
    [sql_create appendString:@" text,"];
    [sql_create appendString:clumName_JRFMdbVer];
    [sql_create appendString:@" long,"];
    [sql_create appendFormat:@" primary key(%@))",clumName_JRFMdbName];
    [db executeUpdate:sql_create];
    
    //replaceTable
    NSDictionary* dict = @{clumName_JRFMdbVer:@(ver),clumName_JRFMdbName:clumKey_JRFMdb};
    NSString* sql_replace = [self Sql_ReplaceDict:dict forTable:tableName_JRFMdbVer];
    [db executeUpdate:sql_replace withParameterDictionary:dict];
}

//表操作
+ (BOOL)JRFMDB_addClumName:(NSString *)clumName ClunType:(NSString *)clunType forDB:(JRFMDatabase *)db forTable:(NSString *)tableName
{
    NSMutableString* sql_add = [NSMutableString string];
    [sql_add appendFormat:@"ALTER TABLE `%@` ADD `%@` %@",tableName,clumName,clunType];
    return [db executeUpdate:sql_add];
}

+ (BOOL)JRFMDB_modifyTableName:(NSString*)oldTableName toTableName:(NSString*)tableName forDB:(JRFMDatabase*)db
{
    NSMutableString* sql = [NSMutableString string];
    [sql appendFormat:@"ALTER TABLE '%@' RENAME TO %@;",oldTableName,tableName];
    return [db executeUpdate:sql];
}

@end
